***********************************
*Changes in the original PACE file*
***********************************
clear all
***Convert Excel to dta file***
import excel "PACE_four_digit_2.xlsx",firstrow sheet("Blad2")
merge 1:m sic1987 using "SIC_NACE_correspondence.dta"

keep if _merge==3

drop if FROMNBER=="NOT IN PACE"

drop _merge

save "PACE_sni2007.dta", replace
*******************************

clear all
use "PACE_sni2007.dta"



replace PACE_CAPX=0 if PACE_CAPX==.
replace PACE_OPX=0 if PACE_OPX==.

*Sum variables according to SNI2007*

bysort sni2007_4d: egen sum_PACE=total(Total_PACE)
bysort sni2007_4d: egen sum_sales=total(sum_total_shipments)
g ratio_pace_sales=sum_PACE/sum_sales

*I keep the OPEX and CAPEX if we want to inspect the data more carefully later*
bysort sni2007_4d: egen sum_CAPX=total(PACE_CAPX)
bysort sni2007_4d: egen sum_OPX=total(PACE_OPX)

*Drop duplicates in terms of SNI2007*
duplicates drop sni2007_4d, force

keep sni2007_4d  sic1987 sum_PACE sum_sales ratio_pace_sales sum_CAPX sum_OPX

save "PACE_sni2007_stat_2.dta", replace



